<?php
namespace app\store\controller;
/**
 * Created by PhpStorm.
 * task: Administrator
 * Date: 2019/11/28 0028
 * Time: 19:12
 */
use think\Controller;
use think\Db;
class Excelexport extends Controller
{
        protected $accounts_info= 'accounts_info';                                      /* 道具日志表 */
        protected $user_game_info = 'user_game_info';                                    /* 游戏用户表 */
        protected $user_tradelog = 'tradelog';
        protected $stat_remain = 'stat_remain';                                         /* 数据统计表 */
        protected $table_DB_Platform_Account_library = 'db_platform_accounts';          /*平台账户配置库*/
        protected function Db_connect_accounts_info(){
            $accounts_info= Db::connect($this->table_DB_Platform_Account_library)->table($this->accounts_info);
            return $accounts_info;
        }
        protected function Db_connect_stat_remain()
        {
            $stat_remain = Db::connect($this->table_DB_Platform_Account_library)->table($this->stat_remain);
            return $stat_remain;
        }
        protected function Db_connect_user_tradelog(){
            $user_tradelog= Db::connect($this->table_DB_Platform_Account_library)->table($this->user_tradelog);
            return $user_tradelog;
        }
        protected function Db_connect_user_game_info()
        {
            $user_game_info= Db::connect($this->table_DB_Platform_Account_library)->table($this->user_game_info);
            return $user_game_info;
        }
        /**
         * 订单Excel导出
         * excel订单表格表格导出
         * @author static7  */
        function user_down_recharge(){
            $data['data'] =$this->Db_connect_user_tradelog()
                ->where($this->structure_where())
                ->order('createdat desc')
                ->select();
            $list=[];
            foreach($data['data'] as $k=>$v){
                $list[$k]['id'] = $v['id'];
                $list[$k]['trade_name'] = get_pay_type($v['tradetype']);
                $list[$k]['userid'] = $v['userid'];
                $channel_name =get_channel_name($v['channel_id']);
                if($channel_name==0){$channel_name ='官方-0-渠道';}
                $list[$k]['channel_name'] = $channel_name;
                $list[$k]['amount'] = $v['amount'];
                $list[$k]['pay_amount'] = $v['amount'];
                $list[$k]['goods_id'] = $v['goods_id'];
                $list[$k]['goods_name'] = get_Billing_name($v['goods_id']);
                if($v['status'] ==1){
                    $list[$k]['status']='已充值';
                }else{
                    $list[$k]['status']='未充值';
                }
                $list[$k]['createdat'] = $v['createdat'];
                $list[$k]['paydate'] = $v['paydate'];
             }
             $name = '订单明细';
             $header = ['订单ID', '支付方式','用户ID','渠道','配置金额','实付金额','商品ID','商品名','状态','发起订单时间','完成订单时间'];
             $width = [30,15,15,15,15,15,15,30,15,30,30];
             excelExport($name, $header, $list,$width);
        }
    /**
     * 用户Excel导出
     * excel订单表格表格导出
     * @author static7  */
    function user(){
        $data['data'] =$this->Db_connect_accounts_info()
            ->where($this->structure_user_where())
            ->order('RegisterDate desc')
            ->select();
        $list=[];
        foreach($data['data'] as $k=>$v){
            $list[$k]['UserID'] = $v['UserID'];
            $channel_name =get_channel_name($v['SpreaderID']);
            $list[$k]['channel_name'] = $channel_name;
            $list[$k]['NickName'] = $v['NickName'];
            $list[$k]['VipLevel'] = $v['VipLevel'];
            $list[$k]['TotalRecharge'] = $v['TotalRecharge'];
            $Cannon =$this->Db_connect_user_game_info()
                ->where('UserID',$v['UserID'])
                ->value('Cannon');
            $list[$k]['Cannon'] = $Cannon;
            $list[$k]['Score'] = $v['Score'];
            $list[$k]['Diamond'] = $v['Diamond'];
            $list[$k]['Lottery'] = $v['Lottery'];
            $list[$k]['RegisterDate'] = $v['RegisterDate'];
            $list[$k]['LastLoginDate'] = $v['LastLoginDate'];
            $list[$k]['ClientVersion'] = $v['ClientVersion'];
        }
        $name = '用户EXCEL';
        $header = ['玩家ID', '来源渠道','昵称','VIP等级','总充值','炮倍','金币','钻石','鱼卷','注册时间','最后登录时间','最后登录版本号'];
        $width = [30,15,15,15,15,15,15,30,15,30,30,30];
        excelExport($name, $header, $list,$width);
    }
    /**
     * 日报导出
     * excel订单表格表格导出
     * @author static7  */
    function Daily_report_down(){
        $data = $this->Db_connect_stat_remain()
            ->where($this->Daily_report_where())
            ->order('stat_time desc')
            ->select();
        $list=[];
        foreach($data as $k=>$v){
            $list[$k]['date']  = date('Y-m-d', strtotime($v['stat_time']));
            $channel_name =get_channel_name($v['channel']);
            if($channel_name==888){$channel_name ='全部渠道';}
            $list[$k]['channel_name']  =$channel_name;
            $list[$k]['dru']  =$v['dru'];
            $list[$k]['dayactivity']  =$v['dayactivity'];
            $list[$k]['allmoney']  =$v['allmoney'];
            $list[$k]['rechange_num']  =$v['rechange_num'];
            $list[$k]['order_num']  =$v['order_num'];
            $list[$k]['avg_pay_num']  =$v['avg_pay_num'];
            $list[$k]['pay_rate']  =($v['pay_rate']*100).'%';
            $list[$k]['arpu']  =$v['arpu'];
            $list[$k]['arppu']  =$v['arppu'];
            $list[$k]['addrechrge_amount']  =$v['addrechrge_amount'];
            $list[$k]['addrechrge_number']  =$v['addrechrge_number'];
            $list[$k]['add_order_num']  =$v['add_order_num'];
            $list[$k]['add_pay_rate']  =($v['add_pay_rate']*100).'%';
            $list[$k]['addarpu']  =$v['addarpu'];
            $list[$k]['addarppu']  =$v['addarppu'];
            $list[$k]['user_online_avg_time']  =$v['user_online_avg_time'].'分钟';
            $list[$k]['add_user_online_avg_time']  =$v['add_user_online_avg_time'].'分钟';
            if($v['second_day']==0){$v['second_day'] ='--';}else{$v['second_day']=($v['second_day']*100).'%';}
            if($v['third_day']==0){$v['third_day'] ='--';}else{$v['third_day']=($v['third_day']*100).'%';}
            if($v['seventh_day']==0){$v['seventh_day'] ='--';}else{$v['seventh_day']=($v['seventh_day']*100).'%';}
            if($v['fifth_day']==0){$v['fifth_day'] ='--';}else{$v['fifth_day']=($v['fifth_day']*100).'%';}
            $list[$k]['second_day']  =$v['second_day'];
            $list[$k]['third_day']  =$v['third_day'];
            $list[$k]['seventh_day']  =$v['seventh_day'];
            $list[$k]['fifth_day']  =$v['fifth_day'];
        }
        $name = '日报';
        $header = [
            '日期', '渠道','新增用户','活跃用户','付费金额','付费用户','付费次数','人均付费次数',
            '付费率','ARPU','ARPPU', '新用户付费金额','新用户付费人数','新用户付费次数','新用户付费率',
            '新用户ARPU','新用户ARPPU','活跃用户日平均游戏时长(分钟)','新增用户日平均游戏时长(分钟)',
            '次日留存','	三日留存','七日留存','十四日留存'
        ];
        $width = [15,15,15,15,15,15,15,15,15,15,15,20,20,20,20,20,20,40,40,15,15,15,15];
        excelExport($name, $header, $list,$width);
    }
    protected function structure_user_where()
    {
        $reg_time = $this->request->param('reg_time');
        $login_time = $this->request->param('login_time');
        $channel_type    = $this->request->param('channel_type');
        $UserID    = $this->request->param('UserID');
        $reg_ip    = $this->request->param('reg_ip');
        $login_ip    = $this->request->param('login_ip');
        $UniqueID    = $this->request->param('UniqueID');
        $ClientVersion    = $this->request->param('ClientVersion');
        $NickName    = $this->request->param('NickName');
        $where = [];
        if(!empty($UserID)){
            $where[] =['UserID','eq',$UserID];
        }
        if(!empty($NickName)){
            $where[] =['NickName','like',"%".$NickName."%"];
        }
        if(!empty($UniqueID)){
            $where[] =['UniqueID','eq',$UniqueID ];
        }
        if(!empty($reg_ip)){
            $where[] =['RegisterIP','eq',$reg_ip];
        }
        if(!empty($login_ip)){
            $where[] =['LastLoginIP','eq',$login_ip];
        }
        if(!empty($ClientVersion)){
            $where[] =['ClientVersion','eq',$ClientVersion];
        }
        if (!empty($reg_time)) {
            $list = explode(' - ', $reg_time);
            $begin = $list[0];
            $after = $list[1];
            $where[] = ['RegisterDate', 'between', [$begin, $after.' 23:59:59']];
        }
        if (!empty($login_time)) {
            $list = explode(' - ', $login_time);
            $begin = $list[0];
            $after = $list[1];
            $where[] = ['LastLoginDate', 'between', [$begin, $after.' 23:59:59']];
        }
        if(!empty($channel_type)){
            if($channel_type==30)$channel_type=0;
            $where[] =['SpreaderID','eq',$channel_type];
        }
        return $where;
    }
    protected function structure_where(){
            $pay_state    = $this->request->param('pay_state');
            $UserID    = $this->request->param('UserID');
            $order_no    = $this->request->param('order_no');
            $billing_type    = $this->request->param('billing_type');
            $pay_type    = $this->request->param('pay_type');
            $channel_type    = $this->request->param('channel_type');
            $pay_at    = $this->request->param('pay_at');
            $where=[];
            if(!empty($UserID)){
                $where[] =['userid','eq',$UserID];
            }
            if(!empty($order_no)){
                $where[] =['id','eq',$order_no];
            }
            if(!empty($billing_type)){
                $where[] =['goods_id','eq',$billing_type];
            }
            if(!empty($pay_type)){
                $where[] =['tradetype','eq',$pay_type];
            }
            if(!empty($pay_at)){
                $list = explode(' - ',$pay_at);
                $begin =$list[0];
                $after =$list[1];
                $where[] =['createdat','between',[$begin,$after]];
            }
            if(!empty($channel_type)){
                if($channel_type==30)$channel_type=0;
                $where[] =['channel_id','eq',$channel_type];
            }
            if(!empty($pay_state)){
                if($pay_state==2)$pay_state=0;
                $where[] =['status','eq',$pay_state];
            }
            return $where;
    }
    protected function Daily_report_where()
    {
        $channel_type = $this->request->param('channel_type');
        $search_time = $this->request->param('search_time');
        $where = [];
        if (!empty($search_time)) {
            $list = explode(' - ', $search_time);
            $begin = $list[0];
            $after = $list[1];
            $where[] = ['stat_time', 'between', [$begin, $after]];
        } else {
            $begin = date('Y-m-d', time() - 7 * 24 * 3600);
            $after = date('Y-m-d', time() - 24 * 3600);
            $where[] = ['stat_time', 'between', [$begin, $after]];
        }
        if (!empty($channel_type)) {
            if ($channel_type == 30) $channel_type = 0;
            $where[] = ['channel', 'eq', $channel_type];
        } else {
            $where[] = ['channel', 'eq', 888];
        }
        return $where;
    }

}